package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.LinkedList;
import java.util.List;
import util.UIDGenerator;
import domain.Comentario;

public class JDBCComentarioDAO implements IComentarioDAO{
	public void deleteComentario(String comentarioID){
		Connection conn = ConnectionManager.getInstance().checkOut();
		System.out.println("Conexion abierta");
		String sql = "DELETE FROM comentarios WHERE (comentarioOID = ?)";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, comentarioID);
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
        	System.out.println("Conexion cerrada");
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }		
	}

	public void insertComentario(String usuarioID, Comentario c){
		Connection conn = ConnectionManager.getInstance().checkOut();
		System.out.println("Conexion abierta");
		String sql = "INSERT INTO comentarios(comentarioOID, usuarioOID, byUsuarioOID, comentario, fechaComentario) VALUES (?, ?, ?, ?)";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, UIDGenerator.getInstance().getKey());
            stmt.setString(2, usuarioID);
            stmt.setString(3, c.getByUsuarioID());
            stmt.setString(4, c.getComentario());
            stmt.setDate(5, (Date)c.getFechaComentario());

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
        	ConnectionManager.getInstance().checkIn(conn);
        	System.out.println("Conexion cerrada");
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}

	public List<Comentario> selectAllComentarios(String nick){
		Connection conn = ConnectionManager.getInstance().checkOut();
		System.out.println("Conexion abierta");
        PreparedStatement stmt = null;
        ResultSet result = null;
        List<Comentario> listaComentarios = new LinkedList<Comentario>();
        String sql = "SELECT * FROM comentarios c, usuarios u WHERE (u.nick = ?) AND u.usuarioOID = c.usuarioOID";
        
        try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nick);
			result = stmt.executeQuery();
			
			while (result.next()) {
				Comentario c = new Comentario();
				c.setComentarioID(result.getString("comentarioOID"));
				c.setUsuarioID(result.getString("usuarioOID"));
				c.setByUsuarioID(result.getString("byUsuarioOID"));
				c.setComentario(result.getString("comentario"));
				c.setFechaComentario(result.getDate("fechaComentario"));
								
				listaComentarios.add(c);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			System.out.println("Conexion cerrada");
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return listaComentarios;
    }	
}
